MANVIA BLOGExport to excel with formating option
-
Packages
npm install --save exceljs@1.12.0 npm install --save file-saver -
Coding
1. tsconfig.js
add
"paths": { "exceljs": [ "node_modules/exceljs/dist/exceljs.min" ] }, complete code
{ "compileOnSave": false, "compilerOptions": { "baseUrl": "./", "outDir": "./dist/out-tsc", "sourceMap": true, "declaration": false, "moduleResolution": "node", "emitDecoratorMetadata": true, "experimentalDecorators": true, "target": "es5", "paths": { "exceljs": [ "node_modules/exceljs/dist/exceljs.min" ] }, "typeRoots": [ "node_modules/@types" ], "lib": [ "es2017", "dom" ] } } 2. excel.service.ts
import { Injectable } from '@angular/core'; import { Workbook } from 'exceljs'; import * as fs from 'file-saver'; import * as logoFile from './carlogo.js'; import { DatePipe } from '../../node_modules/@angular/common'; @Injectable({ providedIn: 'root' }) export class ExcelService { constructor(private datePipe: DatePipe) { } generateExcel() { //Excel Title, Header, Data const title = 'Car Sell Report'; const header = ["Year", "Month", "Make", "Model", "Quantity", "Pct"] const data = [ [2007, 1, "Volkswagen ", "Volkswagen Passat", 1267, 10], [2007, 1, "Toyota ", "Toyota Rav4", 819, 6.5], [2007, 1, "Toyota ", "Toyota Avensis", 787, 6.2], [2007, 1, "Volkswagen ", "Volkswagen Golf", 720, 5.7], [2007, 1, "Toyota ", "Toyota Corolla", 691, 5.4], [2007, 1, "Peugeot ", "Peugeot 307", 481, 3.8], [2008, 1, "Toyota ", "Toyota Prius", 217, 2.2], [2008, 1, "Skoda ", "Skoda Octavia", 216, 2.2], [2008, 1, "Peugeot ", "Peugeot 308", 135, 1.4], [2008, 2, "Ford ", "Ford Mondeo", 624, 5.9], [2008, 2, "Volkswagen ", "Volkswagen Passat", 551, 5.2], [2008, 2, "Volkswagen ", "Volkswagen Golf", 488, 4.6], [2008, 2, "Volvo ", "Volvo V70", 392, 3.7], [2008, 2, "Toyota ", "Toyota Auris", 342, 3.2], [2008, 2, "Volkswagen ", "Volkswagen Tiguan", 340, 3.2], [2008, 2, "Toyota ", "Toyota Avensis", 315, 3], [2008, 2, "Nissan ", "Nissan Qashqai", 272, 2.6], [2008, 2, "Nissan ", "Nissan X-Trail", 271, 2.6], [2008, 2, "Mitsubishi ", "Mitsubishi Outlander", 257, 2.4], [2008, 2, "Toyota ", "Toyota Rav4", 250, 2.4], [2008, 2, "Ford ", "Ford Focus", 235, 2.2], [2008, 2, "Skoda ", "Skoda Octavia", 225, 2.1], [2008, 2, "Toyota ", "Toyota Yaris", 222, 2.1], [2008, 2, "Honda ", "Honda CR-V", 219, 2.1], [2008, 2, "Audi ", "Audi A4", 200, 1.9], [2008, 2, "BMW ", "BMW 3-serie", 184, 1.7], [2008, 2, "Toyota ", "Toyota Prius", 165, 1.6], [2008, 2, "Peugeot ", "Peugeot 207", 144, 1.4] ]; //Create workbook and worksheet let workbook = new Workbook(); let worksheet = workbook.addWorksheet('Car Data'); //Add Row and formatting let titleRow = worksheet.addRow([title]); titleRow.font = { name: 'Comic Sans MS', family: 4, size: 16, underline: 'double', bold: true } worksheet.addRow([]); let subTitleRow = worksheet.addRow(['Date : ' + this.datePipe.transform(new Date(), 'medium')]) //Add Image let logo = workbook.addImage({ base64: logoFile.logoBase64, extension: 'png', }); worksheet.addImage(logo, 'E1:F3'); worksheet.mergeCells('A1:D2'); //Blank Row worksheet.addRow([]); //Add Header Row let headerRow = worksheet.addRow(header); // Cell Style : Fill and Border headerRow.eachCell((cell, number) => { cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FFFFFF00' }, bgColor: { argb: 'FF0000FF' } } cell.border = { top: { style: 'thin' }, left: { style: 'thin' }, bottom: { style: 'thin' }, right: { style: 'thin' } } }) // worksheet.addRows(data); // Add Data and Conditional Formatting data.forEach(d => { let row = worksheet.addRow(d); let qty = row.getCell(5); let color = 'FF99FF99'; if (+qty.value < 500) { color = 'FF9999' } qty.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: color } } } ); worksheet.getColumn(3).width = 30; worksheet.getColumn(4).width = 30; worksheet.addRow([]); //Footer Row let footerRow = worksheet.addRow(['This is system generated excel sheet.']); footerRow.getCell(1).fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FFCCFFE5' } }; footerRow.getCell(1).border = { top: { style: 'thin' }, left: { style: 'thin' }, bottom: { style: 'thin' }, right: { style: 'thin' } } //Merge Cells worksheet.mergeCells(`A${footerRow.number}:F${footerRow.number}`); //Generate Excel File with given name workbook.xlsx.writeBuffer().then((data) => { let blob = new Blob([data], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' }); fs.saveAs(blob, 'CarData.xlsx'); }) } } 3. in component
import { Component } from '@angular/core'; import { ExcelService } from './excel.service'; Component({ selector: 'app-root', templateUrl: './app.component.html', styleUrls: ['./app.component.css'] }) export class AppComponent { constructor(private excelService: ExcelService) { } generateExcel() { this.excelService.generateExcel(); } } 4. in html
<button (click)="generateExcel()"> Generate Excel</button> 5. in module
import { CommonModule, DatePipe } from '@angular/common'; import { ExcelService } from '../core/services/excel.service'; .... ... .... providers: [{provide: OWL_DATE_TIME_LOCALE, useValue: 'en-IN'},DocumentEntryService,CommonService,ExcelService ,Excel1Service, DatePipe] -
Live example with db data
Service
import { Injectable } from '@angular/core'; import { Workbook } from 'exceljs'; import * as fs from 'file-saver'; import * as logo from './logo.js'; export class ExcelCustomerService { constructor() { } exportAsExcelFile(a,b,c){ } exportExcel(excelData) { //Title, Header & Data const title = excelData.title; const header = excelData.headers const data = excelData.data; //Create a workbook with a worksheet let workbook = new Workbook(); let worksheet = workbook.addWorksheet('Report'); //Add Row and formatting worksheet.mergeCells('B1', 'J2'); let titleRow = worksheet.getCell('B1'); titleRow.value = title titleRow.font = { name: 'Calibri', size: 16, underline: 'single', bold: true, color: { argb: '0085A3' } } titleRow.alignment = { vertical: 'middle', horizontal: 'left' } // Date // worksheet.mergeCells('G1:H4'); let d = new Date(); let date = d.getDate() + '-' + (d.getMonth()+1) + '-' + d.getFullYear() +' '+ d.getHours()+':'+d.getMinutes()+':'+d.getSeconds(); // let dateCell = worksheet.getCell('G1'); // dateCell.value = date; // dateCell.font = { // name: 'Calibri', // size: 12, // bold: true // } // dateCell.alignment = { vertical: 'middle', horizontal: 'center' } //Add Image let myLogoImage = workbook.addImage({ base64: logo.imgBase64, extension: 'png', }); worksheet.mergeCells('A1:A3'); worksheet.addImage(myLogoImage, 'A1:A3'); //subtitle worksheet.mergeCells('B3', 'J3'); let subTitleRow = worksheet.getCell('B3'); subTitleRow.value = excelData.subtitle; //Blank Row worksheet.addRow([]); //Adding Header Row let headerRow = worksheet.addRow(header); headerRow.eachCell((cell, number) => { cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: '0085A3' }, bgColor: { argb: '' } } cell.font = { bold: true, color: { argb: 'FFFFFF' }, size: 12 } }) // Adding Data with Conditional Formatting data.forEach(d => { let row = worksheet.addRow(d); // let sales = row.getCell(6); // let color = 'FF99FF99'; // if (+sales.value < 200000) { // color = 'FF9999' // } // sales.fill = { // type: 'pattern', // pattern: 'solid', // fgColor: { argb: color } // } } ); worksheet.getColumn(1).width = 20; worksheet.getColumn(2).width = 20; worksheet.getColumn(3).width = 20; worksheet.getColumn(4).width = 20; worksheet.getColumn(5).width = 20; worksheet.getColumn(6).width = 20; worksheet.getColumn(7).width = 20; worksheet.getColumn(8).width = 20; worksheet.getColumn(9).width = 20; worksheet.getColumn(10).width = 20; worksheet.addRow([]); //Footer Row let footerRow = worksheet.addRow(['Customer report generated at ' + date]); footerRow.getCell(1).fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FFB050' } }; //Merge Cells worksheet.mergeCells(`A${footerRow.number}:J${footerRow.number}`); //Generate & Save Excel File workbook.xlsx.writeBuffer().then((data) => { let blob = new Blob([data], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' }); fs.saveAs(blob, title + '.xlsx'); }) } } in component
exportAsExcel():void { var headerInfo = ['first_name', 'last_name', 'email', 'date', 'first_in', 'last_out', 'on_duty_hours']; this.searchForm.patchValue({'page':1, 'export' :1}); if(this.searchForm.get('from_date_f').value && this.searchForm.get('from_date_f').value.getFullYear()>0){ var month=(parseInt(this.searchForm.get('from_date_f').value.getMonth())+1); var from_date_f=this.searchForm.get('from_date_f').value.getFullYear()+'-'+month+'-'+ this.searchForm.get('from_date_f').value.getDate(); this.searchForm.get('from_date').patchValue(from_date_f); } if(this.searchForm.get('to_date_f').value && this.searchForm.get('to_date_f').value.getFullYear()>0){ var month=(parseInt(this.searchForm.get('to_date_f').value.getMonth())+1); var to_date_f=this.searchForm.get('to_date_f').value.getFullYear()+'-'+month+'-'+ this.searchForm.get('to_date_f').value.getDate(); this.searchForm.get('to_date').patchValue(to_date_f); } this.searchForm.patchValue({'section':this.router.url.split('?')[0]}); this.commonService.getAll('report/customer',this.searchForm.value) .subscribe(response => { //this.excelService.exportAsExcelFile(response.data, 'customer_report', []); let dataForExcel = []; let empPerformance = response.data; empPerformance.forEach((row: any) => { dataForExcel.push(Object.values(row)) }) let reportData = { title: 'Customer Report', subtitle:response.reportTitle, data: dataForExcel, headers: Object.keys(empPerformance[0]) } this.excelService.exportExcel(reportData); document.getElementById("overlay").style.display = "none" }); }